﻿using Dapper;
using Entity;
using IService;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Service
{
    public class ProductService : IProductService
    {
        private readonly IDbConnection _dbConnection;

        public ProductService(IDbConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }

        public async Task<PagedViewModel<PurchaseOrderDetailDTO>> GetProductListAsync(
            string? codeOrName = null,
            string? unit = null,
            int page = 1,
            int size = 10)
        {
            var where = new List<string> { "p.is_delete = '0'" };
            if (!string.IsNullOrEmpty(codeOrName))
                where.Add("(p.product_code LIKE @codeOrName OR p.product_name LIKE @codeOrName)");
            if (!string.IsNullOrEmpty(unit))
                where.Add("p.unit = @unit");

            var sql = $@"
                SELECT 
                    p.Product_ID, p.product_name, p.product_code,
                    p.specification_model, p.unit, p.type_of_goods,
                    p.purchase_unit_price, du.DictLabel AS unit_name,
                    dt.DictLabel AS type_of_goods_name, 0 AS prod_num,
                    0 AS stkIn_num, 0 AS uninbound_quantity
                FROM wms_basic_product_information p
                LEFT JOIN wms_Dictionary_data du ON p.unit = du.DictValue AND du.DictTypeCode = 'Unit'
                LEFT JOIN wms_Dictionary_data dt ON p.type_of_goods = dt.DictValue AND dt.DictTypeCode = 'GoodsType'
                WHERE {string.Join(" AND ", where)}
                ORDER BY p.product_name
                OFFSET @skip ROWS FETCH NEXT @size ROWS ONLY
            ";

            var countSql = $"SELECT COUNT(1) FROM wms_basic_product_information p WHERE {string.Join(" AND ", where)}";

            var param = new
            {
                codeOrName = string.IsNullOrEmpty(codeOrName) ? null : $"%{codeOrName}%",
                unit,
                skip = (page - 1) * size,
                size
            };

            var items = await _dbConnection.QueryAsync<PurchaseOrderDetailDTO>(sql, param);
            var total = await _dbConnection.ExecuteScalarAsync<int>(countSql, param);

            return new PagedViewModel<PurchaseOrderDetailDTO> { items = items.ToList(), total = total };
        }
    }
}
